import numpy as np
import tensorflow as tf
from tensorflow.keras.layers import Dense,SimpleRNN
import matplotlib.pyplot as plt
import os
import pymysql
from tensorflow import keras

def get_ssq_db():
    # 建立数据库连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='spider_db')
    # 创建游标对象
    cursor = conn.cursor()
    # 执行 SQL 查询语句
    sql = "SELECT red_1,red_2,red_3,red_4,red_5,red_6,blue_1,qihao FROM ssq_info ORDER BY qihao"
    cursor.execute(sql)
    # 获取查询结果
    result = cursor.fetchall()
    lst=[]
    # i=0   
    for row in result:    
        red1=row[0]
        red2=row[1]
        red3=row[2]
        red4=row[3]
        red5=row[4]
        red6=row[5]
        blue1=row[6]
        ls=[red1,red2,red3,red4,red5,red6,blue1]
        lst.append(ls)

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()
    return lst

def get_ssq_by_qihao(qihao):
    # 建立数据库连接
    conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='spider_db')
    # 创建游标对象
    cursor = conn.cursor()
    # 执行 SQL 查询语句
    # sql = f"SELECT red_1,red_2,red_3,red_4,red_5,red_6,blue_1,qihao FROM ssq_info where qihao='{qihao}' ORDER BY qihao ;"
    sql = f"SELECT red_1,red_2,red_3,red_4,red_5,red_6,blue_1,qihao FROM ssq_info WHERE qihao < '{qihao}' ORDER BY qihao DESC LIMIT 1;"
    print(sql)
    cursor.execute(sql)
    # 获取查询结果
    result = cursor.fetchall()
    lst=[]
    lst=[]
    # i=0   
    for row in result:    
        red1=row[0]
        red2=row[1]
        red3=row[2]
        red4=row[3]
        red5=row[4]
        red6=row[5]
        blue1=row[6]
        ls=[red1,red2,red3,red4,red5,red6,blue1]
        lst.append(ls)  

    # 关闭游标和数据库连接
    cursor.close()
    conn.close()
    #重新升序排列
    # lst.reverse()
    return lst

# x=get_ssq_db()
# print(x)
# exit()

# x=get_ssq_by_qihao('2023105')
# print(x)
# exit()

# input_word='abcde'
# w_to_id={'a':0,'b':1,'c':2,'d':3,'e':4}
# id_to_onehot={0:[1,0,0.,0,0],1:[0,1,0,0.,0],2:[0,0,1,0.,0],3:[0,0,0.,1,0],4:[0,0,0.,0,1]}

x_train=[]
y_train=[]

ssq_db_lst=get_ssq_db()  #数据库中所有的双色球历史数据

train_db=ssq_db_lst

train_db=tf.one_hot(train_db,depth=34,axis=2,on_value=1.0)


for i in range(1,len(train_db)):
    x_train.append(train_db[i-1:i])
    y_train.append(train_db[i])


# y_train=[
#     w_to_id['e'],
#     w_to_id['a'],
#     w_to_id['b'],
#     w_to_id['c'],
#     w_to_id['d'],
# ]

# np.random.seed(7)
# np.random.shuffle(x_train)
# np.random.seed(7)
# np.random.shuffle(y_train)
# tf.random.set_seed(7)

# x_train=np.reshape(x_train, (len(x_train),8,33))
x_train=np.array(x_train)
x_train=tf.squeeze(x_train)
y_train=np.array(y_train)

print(x_train.shape)
print(y_train.shape)

# exit()

# model=tf.keras.Sequential([
#     SimpleRNN(8,return_sequences=True),
#     # Dense(6,activation='softmax')
# ])

model = keras.Sequential()
model.add(keras.layers.SimpleRNN(16,input_shape=(7,34),return_sequences=True))
# model.add(keras.layers.GlobalAveragePooling1D())
model.add(keras.layers.Dense(34,activation='softmax'))
# model.add(keras.layers.Embedding(133, 16))
# model.add(keras.layers.GlobalAveragePooling1D())
# model.add(keras.layers.Dense(16, activation='relu'))
# model.add(keras.layers.Dense(1, activation='sigmoid'))

model.summary()
# exit()

model.compile(optimizer=tf.keras.optimizers.Adam(0.01),
              loss=tf.keras.losses.CategoricalCrossentropy(from_logits=False),
              metrics=['categorical_accuracy'])

# print(os.path.dirname(os.path.abspath("checkpoint/rnn_onhot.ckpt")))
checkpoint_save_path=os.path.abspath("checkpoint/ssq_7pre7.ckpt")
# print(checkpoint_save_path)
if os.path.exists(checkpoint_save_path+'.index'):
    print('-------------------load the model-------------------')
    model.load_weights(checkpoint_save_path)

cp_callback=tf.keras.callbacks.ModelCheckpoint(filepath=checkpoint_save_path,
                                                save_weights_only=True,
                                                save_best_only=True,
                                                monitor='loss')       

history=model.fit(x_train,y_train,batch_size=32,epochs=10,callbacks=[cp_callback])

# exit()

def save_weight():
    file=open('./weights_7pre7.txt','w')
    for v in model.trainable_variables:
        file.write(str(v.name)+'\n')
        file.write(str(v.shape)+'\n')
        file.write(str(v.numpy())+'\n')
    file.close()

def print_curve(history):
    #显示训练集和验证集的acc和loss曲线
    acc=history.history['categorical_accuracy']
    loss=history.history['loss']

    plt.subplot(1,2,1)
    plt.plot(acc,label='训练集的accuracy')
    plt.title('训练集的accuracy')
    plt.legend()

    plt.subplot(1,2,2)
    plt.plot(loss,label='训练集的loss')
    plt.title('训练集的loss')
    plt.legend()
    plt.show()

print_curve(history)
# exit()

# #预测值
# def predict():
#     preNum=None
#     while preNum!='\r':
#         preNum=input("输入当前期号：")
#         predict_db=get_ssq_by_qihao(preNum)

#         predict_db=tf.one_hot(predict_db,depth=34,axis=2,on_value=1.0)
#         # predict_db=tf.squeeze(predict_db)
    
#         # print(predict_db)
#         # exit()

#         result=model.predict([predict_db])
#         # print(result.shape)
#         # exit()
#         pred=tf.argmax(result,axis=2)
#         # print(pred)

#         print('期号：'+preNum)
#         print(pred)
#         real_db=get_ssq_by_qihao(int(preNum)+1)
#         print('实际：',real_db)
#         # pred=int(pred)
#         # tf.print('期号：'+preNum+'预测为->'+(pred))

# predict()
def predict():
    while True:
        preNum=input("输入当前期号：")
        if preNum=='q':
            return
        predict_db=get_ssq_by_qihao(preNum)

        predict_db=tf.one_hot(predict_db,depth=34,axis=2,on_value=1.0)
        # predict_db=tf.squeeze(predict_db)
    
        # print(predict_db)
        # exit()

        result=model.predict([predict_db])
        print(result.shape)
        # exit()
        pred=tf.argmax(result,axis=2)
        
        # pred=int(pred)
        print('期号：'+preNum)
        print(pred)
        real_db=get_ssq_by_qihao(int(preNum)+1)
        print('实际：',real_db)

predict()